package com.tom.springboot3demo.utils;

import com.tom.springboot3demo.vo.ExcelMergeVo2;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.*;

/**
 * 文件上传下载常用方法工具类
 *
 * @author chen_
 */
public class ExcelUtils {


    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) return null;
        String retValue = null;
        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC) {
            retValue = String.valueOf(cell.getNumericCellValue()).trim();
        } else if (cellType == CellType.STRING) {
            retValue = cell.getStringCellValue().trim();
            //retValue = process(cell);

        } else if (cellType == CellType.BOOLEAN) {
            retValue = String.valueOf(cell.getBooleanCellValue()).trim();
        } else if (cellType == CellType.FORMULA) {
            retValue = String.valueOf(cell.getNumericCellValue()).trim();
        } else if (cellType == CellType.BLANK) {
            retValue = "";
        }

        return retValue;
    }


    public static String getNewCellValue(Cell cell) {
        if (cell == null) return null;
        String retValue = null;
        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC) {
            retValue = String.valueOf(cell.getNumericCellValue()).trim();
        } else if (cellType == CellType.STRING) {

            //retValue = cell.getStringCellValue().trim();
            retValue = getFrontStyle(cell);

        } else if (cellType == CellType.BOOLEAN) {
            retValue = String.valueOf(cell.getBooleanCellValue()).trim();
        } else if (cellType == CellType.FORMULA) {
            retValue = String.valueOf(cell.getNumericCellValue()).trim();
        } else if (cellType == CellType.BLANK) {
            retValue = "";
        }

        return retValue;
    }


    /**
     * 获取excel合并区域中的值
     *
     * @param
     * @return
     */
    public static String getExcelValue(ArrayList<ExcelMergeVo2> mergers, Integer rowNum, Integer columnNum) {
        for (ExcelMergeVo2 merger : mergers) {
            Integer startRow = merger.getStartRow();
            Integer endRow = merger.getEndRow();
            Integer startColumn = merger.getStartColumn();
            Integer endColumn = merger.getEndColumn();
            if ((rowNum >= startRow && rowNum <= endRow) && (columnNum >= startColumn && columnNum <= endColumn)) {
                return merger.getExcelValue();
            }
        }
        return "";
    }

    public static String getFrontStyle2(Cell cell, XSSFWorkbook workbook){
        System.out.println(cell.getRichStringCellValue().getString());
        XSSFFont fontAt =
                workbook.getFontAt(cell.getCellStyle().getFontIndex());
        XSSFColor xssfColor = fontAt.getXSSFColor();
        if (xssfColor != null) {
            byte[] rgb = xssfColor.getRGB();
            int r = (rgb[0] < 0 ? rgb[0] + 256 : rgb[0]);
            int g = (rgb[1] < 0 ? rgb[1] + 256 : rgb[1]);
            int b = (rgb[2] < 0 ? rgb[2] + 256 : rgb[2]);
            System.out.println("颜色："+ r + ", " + g + ", " + b);
        } else {
            System.out.println("Font color: default");
        }

        return cell.getRichStringCellValue().getString();

    }


    public static String getFrontStyle(Cell cell){


        //每一个单元格里面的内容是 文本类型的单元格格式
        XSSFRichTextString richTextString = (XSSFRichTextString) cell.getRichStringCellValue();
        int numFormattingRuns = richTextString.numFormattingRuns();

        //富文本是否格式化操作
        if(numFormattingRuns == 0 ){
            return cell.getStringCellValue().trim();
        }

        int strLength = richTextString.getString().length();
        StringJoiner sb = new StringJoiner("");
        for (int i = 0; i < numFormattingRuns; i++) {
            int startIdx = richTextString.getIndexOfFormattingRun(i);
            int endIdx = richTextString.getIndexOfFormattingRun(i + 1);
            if (i == richTextString.numFormattingRuns() - 1) {
                endIdx = richTextString.length();
            }


            XSSFFont xssfFont = richTextString.getFontAtIndex(startIdx);

            XSSFColor color = xssfFont.getXSSFColor();
            String textSegment = richTextString.getString().substring(startIdx, endIdx);
            if (color != null) {
                byte[] rgb = color.getRGB();
                if (rgb != null) {
                    int red = rgb[0] & 0xFF;
                    int green = rgb[1] & 0xFF;
                    int blue = rgb[2] & 0xFF;

                    /*rgb 红色*/
                    /*color: rgb(255, 0, 0);*/

                    /*rgb 黑色 默认颜色*/
                    /*color: rgb(0,0,0);*/

                    if(red == 255&&green==0&&blue ==0){
                        sb.add("<span style=\"color:#FFEB3B\">"+textSegment+"</span>");

                    }else {
                        sb.add(textSegment);

                    }


                } else {
                    sb.add(textSegment);

                }
            } else {
                sb.add(textSegment);
            }

        }

        return sb.toString();
    }



    /**
     * 处理富文本字符串
     *
     * @param cell 单元格 {@link Cell}
     * @return 出后的字符串
     */
    public static Cell process(Cell cell) {
        if (cell != null && cell.getCellType() == CellType.STRING) {
            //每一个单元格里面的内容是富文本
            XSSFRichTextString richTextString = (XSSFRichTextString) cell.getRichStringCellValue();
            StringJoiner sb = new StringJoiner("");
            Set<String> set = new HashSet<>();

            for (int i = 0; i < richTextString.numFormattingRuns(); i++) {
                int startIdx = richTextString.getIndexOfFormattingRun(i);
                int endIdx = richTextString.getIndexOfFormattingRun(i + 1);
                if (i == richTextString.numFormattingRuns() - 1) {
                    endIdx = richTextString.length();
                }
                XSSFFont xssfFont = richTextString.getFontAtIndex(startIdx);
                if(Objects.isNull(xssfFont))continue;
                XSSFColor color = xssfFont.getXSSFColor();
                String textSegment = richTextString.getString().substring(startIdx, endIdx);
                if(textSegment.contains("/")){
                    textSegment="";
                }
                if (color != null) {
                    byte[] rgb = color.getRGB();
                    if (rgb != null) {
                        int red = rgb[0] & 0xFF;
                        int green = rgb[1] & 0xFF;
                        int blue = rgb[2] & 0xFF;
                        //System.out.println("Text Segment: \"" + textSegment + "\" - Font Color: RGB(" + red + ", " + green + ", " + blue + ")");

                        /*rgb 红色*/
                        /*color: rgb(255, 0, 0);*/

                        if (red == 255 && green == 0 && blue == 0) {
                            String s = "<span style=\"color:red\">" + textSegment + "</span>";
                            sb.add(s);
                            set.add(s);
                            //System.out.println("<span style=\"color:red\">"+textSegment+"</span>");
                        } else {
                            sb.add(textSegment);
                            set.add(textSegment);
                            //System.out.println("<span>"+textSegment+"</span>");
                        }
                        /*rgb 黑色*/
                        /*color: rgb(0,0,0);*/

                    } else {
                        //System.out.println("Text Segment: \"" + textSegment + "\" - Font Color: Default");
                        sb.add(textSegment);
                        set.add(textSegment);
                        //System.out.println("<span>"+textSegment+"</span>");
                    }
                } else {
                    //System.out.println("Text Segment: \"" + textSegment + "\" - Font Color: Default");
                    //System.out.println("<span>"+textSegment+"</span>");
                    sb.add(textSegment);
                    set.add(textSegment);
                }
            }

            System.out.println("添加style样式后的文本内容去重字符串:"+set);
            System.out.println("添加style样式后的文本内容=====>"+sb.toString());

            richTextString.clearFormatting();
            richTextString.append(sb.toString());
            sb.setEmptyValue(new CharSequence() {
                @Override
                public int length() {
                    return 0;
                }

                @Override
                public char charAt(int index) {
                    return 0;
                }

                @Override
                public CharSequence subSequence(int start, int end) {
                    return null;
                }
            });

            return cell;
        } else {

            return cell;
        }
    }


}

